/*************************************************************************
	>      File Name: database.c
	>    Description:	This file 
								  
	>         Author: Yang Xiaohan
	>           Mail: yangxiaohan_niki@163.com 
	>   Created Time: Tue 09 Aug 2022 12:36:28 CST
    >   Changed Time: Tue 09 Aug 2022 12:36:28 CST
 ************************************************************************/

#include <stdio.h>
#include <string.h>
#include <errno.h>
#include "get_data.h"
#include "sqlite3.h"
#include "database.h"

#if 0
int main(int argc, char *argv[])
{
	sqlite3				*db = NULL;
	int					rv = -1;
	char				sample_buf[128];
	char				db_buf[64];
	char				dev_id[64];
	char				time_buf[64];
	float				temp;

	rv = db_open(&db);
	if(rv != 0)
	{
		printf("Open database and create table failure:%s.\n",strerror(errno));
		return -1;
}
	printf("Open database and Create TABLE successfully!\n");

	memset(sample_buf,0,sizeof(sample_buf));
	rv = get_all_data(dev_id,time_buf,&temp,sample_buf);
	if(rv < 0)
	{
		printf("get temp error\n");
		return -1;
	}
	printf("%s|%s|%0.2f\n",dev_id,time_buf,temp);
	printf("get sample ok:%s\n",sample_buf);

	rv = db_insert(db,sample_buf);
	if(rv != 0)
	{
		printf("insert data error\n");
		return -1;
	}
	printf("insert data ok!\n");

	memset(db_buf,0,sizeof(db_buf));
	rv = db_read(db,db_buf);
	if(rv != 0)
	{
		printf("read first data failure\n");
		return -1;
	}
	else
	{
		printf("first data is :%s\n",db_buf);
	}
	db_close(db);

	return 0;
}
#endif

int db_open(sqlite3 **db)
{
	int				 rc = -1;
	char			*errmsg = NULL;
	const char		*sql = NULL;

	rc = sqlite3_open("../tmp/data.db",db);
	if(rc != SQLITE_OK)
	{
		printf("Create and open database failure:%s.\n",sqlite3_errmsg(*db));
		sqlite3_close(*db);
		return -1;
	}
	
	sql = "CREATE TABLE IF NOT EXISTS Temperature(ID int PRIMARY KEY,DATA varchar(255));";

	rc = sqlite3_exec(*db,sql,NULL,NULL,&errmsg);
	if(rc != SQLITE_OK)
	{
		printf("Create table failure:%s.\n",errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(*db);
		return -2;
	}
	return 0;
}

int db_insert(sqlite3 *db,char *sample_buf)
{
	int				rc = -1;
	char			*errmsg = NULL;
	char			sql[128];

	snprintf(sql,sizeof(sql),"INSERT INTO Temperature values(NULL,'%s')",sample_buf);

	rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(SQLITE_OK != rc)
	{
		printf("Insert data to databese failure:%s.\n",errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(db);
		return -3;
	}

	return 0;
}
int db_read(sqlite3 *db,char *db_buf)
{
	int				rc = -1;
	char			*errmsg = NULL;
	char			*sql = NULL;
	char			**result = NULL;
	int				row;

	sql = "SELECT * FROM Temperature order by ID desc LIMIT 0,1";
	
	rc = sqlite3_get_table(db,sql,&result,&row,NULL,&errmsg);
	if(SQLITE_OK != rc)
	{
		printf("show error:%s.\n",errmsg);
		sqlite3_free(errmsg);
		sqlite3_free_table(result);
		sqlite3_close(db);
		return -4;
	}
	if(0 == row)
	{
		printf("table is null\n");
		sqlite3_free_table(result);
		return -5;
	}
	else
	{
		memset(db_buf,0,128);
		snprintf(db_buf,128,"%s",result[3]);
		sqlite3_free_table(result);
	}
	return 0;

}
int db_close(sqlite3 *db)
{
	int			rc = -1;

	rc = sqlite3_close(db);
	if(rc != SQLITE_OK)
	{
		printf("close database failure:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	return 0;
}
